package dao_Impl;

import interfaces.DALException;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import connector.Connector;
import connector.NonStaticConnector;
import dto_objects.AuthorDTO;
import dto_objects.BookDTO;
import dto_objects.PublisherDTO;
import dto_objects.SubjectDTO;

public class MySQLSearchDAO {

	public ArrayList<ArrayList> quickSearch(String search) throws DALException, SQLException{

		ArrayList<BookDTO> list1 = new ArrayList<BookDTO>();
		ArrayList<AuthorDTO> list2 = new ArrayList<AuthorDTO>();
		ArrayList<SubjectDTO> list3 = new ArrayList<SubjectDTO>();
		ArrayList<PublisherDTO> list4 = new ArrayList<PublisherDTO>();

		
		try {
			ResultSet rs1 = Connector.doQuery(
					"SELECT distinct ISBN, title, publisherID, publisher_name, pages FROM Book NATURAL JOIN Publisher WHERE title Like '%"+search+"%'"
					);
			while (rs1.next())
			{
				PublisherDTO pub = new PublisherDTO(rs1.getInt(3), rs1.getString(4));
				
				list1.add(new BookDTO(rs1.getInt(1), pub, rs1.getString(2), rs1.getInt(5)));
			}
			
			ResultSet rs2= Connector.doQuery(
					"SELECT * FROM Author WHERE author_firstname Like '%" + search + "%' " +
							"OR author_lastname Like '%" + search + "%'");
			while (rs2.next())
			{
				list2.add(new AuthorDTO(rs2.getInt(1), rs2.getString(2), rs2.getString(3)));
			}
			ResultSet rs3 = Connector.doQuery(
					"SELECT * FROM Subject " +
							"WHERE subject_name Like '%" + search + "%'");
			while (rs3.next())
			{
				list3.add(new SubjectDTO(rs3.getInt(1), rs3.getString(2)));
			}
			ResultSet rs4 = Connector.doQuery(
					"SELECT * FROM Publisher " +
							"WHERE publisher_name Like '%" + search + "%'");
			while (rs4.next())
			{
				list4.add(new PublisherDTO(rs4.getInt(1), rs4.getString(2)));
			}
			for(BookDTO book: list1){
				List<AuthorDTO> authors = AuthorsFromISBN(book.getISBN());
				List<SubjectDTO> subjects = SubjectsFromISBN(book.getISBN());
				book.setAuthors(authors);
				book.setSubjects(subjects);
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		ArrayList<ArrayList> retur = new ArrayList<ArrayList>();
		retur.add(list1);
		retur.add(list2);
		retur.add(list3);
		retur.add(list4);
		return retur;
		
	}

	public List<BookDTO> advancedSearch(String bookname, String authorFirstName, String authorLastName, String subject, String publisher) throws DALException{
		List<BookDTO> list = new ArrayList<BookDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT distinct ISBN, title, publisherID, publisher_name, pages " +
						"FROM Book NATURAL JOIN Book_Authors NATURAL JOIN Author " +
						"NATURAL JOIN Book_Subjects NATURAL JOIN Subject NATURAL JOIN Publisher " +
						"WHERE title Like '%" + bookname + "%' " +
						"AND author_firstname Like '%" + authorFirstName + "%' " +
						"AND author_lastname Like '%" + authorLastName + "%' " +
						"AND subject_name Like '%" + subject + "%' " +
						"AND publisher_name Like '%" + publisher + "%' ");

		try {
			while (rs.next())
			{
				PublisherDTO pub = new PublisherDTO(rs.getInt(3), rs.getString(4));
				
				list.add(new BookDTO(rs.getInt(1), pub, rs.getString(2), rs.getInt(5)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		
		for(BookDTO book: list){
			List<AuthorDTO> authors = AuthorsFromISBN(book.getISBN());
			List<SubjectDTO> subjects = SubjectsFromISBN(book.getISBN());
			book.setAuthors(authors);
			book.setSubjects(subjects);
		}
		
		return list;
	}

	public List<AuthorDTO> AuthorsFromISBN(int ISBN) throws DALException {
		List<AuthorDTO> list = new ArrayList<AuthorDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT authorID, author_firstname, author_lastname " +
						"FROM Book NATURAL JOIN Book_Authors NATURAL JOIN Author " +
						"WHERE ISBN = " + ISBN );

		try {
			while (rs.next())
			{
				list.add(new AuthorDTO(rs.getInt(1), rs.getString(2), rs.getString(3)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }

		return list;
	}

	public List<SubjectDTO> SubjectsFromISBN(int ISBN) throws DALException {
		List<SubjectDTO> list = new ArrayList<SubjectDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT subjectID, subject_name " +
						"FROM Book NATURAL JOIN Book_Subjects NATURAL JOIN Subject " +
						"WHERE ISBN = " + ISBN );

		try {
			while (rs.next())
			{
				list.add(new SubjectDTO(rs.getInt(1), rs.getString(2)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }

		return list;
	}
	
	public List<BookDTO> SearchISBN(int ISBN) throws DALException{
		List<BookDTO> list = new ArrayList<BookDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT distinct ISBN, title, publisherID, publisher_name, pages " +
						"FROM Book NATURAL JOIN Book_Authors NATURAL JOIN Author " +
						"NATURAL JOIN Book_Subjects NATURAL JOIN Subject NATURAL JOIN Publisher " +
						"WHERE ISBN Like '%" + ISBN + "%' " );

		try {
			while (rs.next())
			{
				PublisherDTO pub = new PublisherDTO(rs.getInt(3), rs.getString(4));
				
				list.add(new BookDTO(rs.getInt(1), pub, rs.getString(2), rs.getInt(5)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		
		for(BookDTO book: list){
			List<AuthorDTO> authors = AuthorsFromISBN(book.getISBN());
			List<SubjectDTO> subjects = SubjectsFromISBN(book.getISBN());
			book.setAuthors(authors);
			book.setSubjects(subjects);
		}
		
		return list;
	}
	
	public List<BookDTO> SearchAuthorID(int authorID) throws DALException{
		List<BookDTO> list = new ArrayList<BookDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT distinct ISBN, title, publisherID, publisher_name, pages " +
						"FROM Book NATURAL JOIN Book_Authors NATURAL JOIN Author " +
						"NATURAL JOIN Book_Subjects NATURAL JOIN Subject NATURAL JOIN Publisher " +
						"WHERE authorID = '" + authorID + "' ");

		try {
			while (rs.next())
			{
				PublisherDTO pub = new PublisherDTO(rs.getInt(3), rs.getString(4));
				
				list.add(new BookDTO(rs.getInt(1), pub, rs.getString(2), rs.getInt(5)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		
		for(BookDTO book: list){
			List<AuthorDTO> authors = AuthorsFromISBN(book.getISBN());
			List<SubjectDTO> subjects = SubjectsFromISBN(book.getISBN());
			book.setAuthors(authors);
			book.setSubjects(subjects);
		}
		
		return list;
	}
	
	public List<AuthorDTO> SearchAuthorIDSingle(int authorID) throws DALException{
		List<AuthorDTO> list = new ArrayList<AuthorDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT distinct authorID, author_firstname, author_lastname " +
						"FROM Author " + "WHERE authorID = '" + authorID + "' ");

		try {
			while (rs.next())
			{
				list.add(new AuthorDTO(rs.getInt(1), rs.getString(2), rs.getString(3)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }

		return list;
	}
	
	public List<BookDTO> SearchSubjectID(int subjectID) throws DALException{
		List<BookDTO> list = new ArrayList<BookDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT distinct ISBN, title, publisherID, publisher_name, pages " +
						"FROM Book NATURAL JOIN Book_Authors NATURAL JOIN Author " +
						"NATURAL JOIN Book_Subjects NATURAL JOIN Subject NATURAL JOIN Publisher " +
						"WHERE subjectID = '" + subjectID + "' ");

		try {
			while (rs.next())
			{
				PublisherDTO pub = new PublisherDTO(rs.getInt(3), rs.getString(4));
				
				list.add(new BookDTO(rs.getInt(1), pub, rs.getString(2), rs.getInt(5)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		
		for(BookDTO book: list){
			List<AuthorDTO> authors = AuthorsFromISBN(book.getISBN());
			List<SubjectDTO> subjects = SubjectsFromISBN(book.getISBN());
			book.setAuthors(authors);
			book.setSubjects(subjects);
		}
		
		return list;
	}
	
	public List<BookDTO> SearchPublisherID(int publisherID) throws DALException{
		List<BookDTO> list = new ArrayList<BookDTO>();
		ResultSet rs = Connector.doQuery(
				"SELECT distinct ISBN, title, publisherID, publisher_name, pages " +
						"FROM Book NATURAL JOIN Book_Authors NATURAL JOIN Author " +
						"NATURAL JOIN Book_Subjects NATURAL JOIN Subject NATURAL JOIN Publisher " +
						"WHERE publisherID = '" + publisherID + "' ");

		try {
			while (rs.next())
			{
				PublisherDTO pub = new PublisherDTO(rs.getInt(3), rs.getString(4));
				
				list.add(new BookDTO(rs.getInt(1), pub, rs.getString(2), rs.getInt(5)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		
		for(BookDTO book: list){
			List<AuthorDTO> authors = AuthorsFromISBN(book.getISBN());
			List<SubjectDTO> subjects = SubjectsFromISBN(book.getISBN());
			book.setAuthors(authors);
			book.setSubjects(subjects);
		}
		
		return list;
	}


}
